﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace BrsdServer
{
    public struct DnsAndState
    {
        public String dns_name;   //与数据库中的dns_name对应，
        public bool dvr_state;   //与数据库中的dns_online字段对应
    }

    public class dnsInfoBLL
    {

        MySqlDBHelper db = new MySqlDBHelper();

        public List<dns_infoEntity> LoadAll()
        {
            string strSql = "select *from user_info";
            List<dns_infoEntity> list = new List<dns_infoEntity>();

            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            MySqlDataReader dr = db.ExecuteReader(cmd);
            while (dr.Read())
            {
                dns_infoEntity dns_info = new dns_infoEntity();
                dns_info.dns_id = dr.GetInt32(0);
                dns_info.user_id = dr.GetInt32(1);
                dns_info.dns_ip = dr.GetString(2);
                dns_info.dns_mac = dr.GetString(3);
                dns_info.dns_update = dr.GetString(4);
                dns_info.dns_online = dr.GetInt32(5);
                dns_info.dns_macbind = dr.GetInt32(6);
                dns_info.dns_name = dr.GetString(7);
                dns_info.dns_location = dr.GetString(8);
                dns_info.dns_tel = dr.GetString(9);
                list.Add(dns_info);
            }
            return list;
        }

        public DataTable LoadAllDataTable()
        {
            //此处的SQL语句与main.aspx页面的GridView绑定的数据顺序一致。
            string strSql = "select dns_ip,dns_mac,dns_port,dns_update,dns_online,dns_location from dns_info";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 取得用户自定义的dns_name;
        /// </summary>
        /// <param name="mac">传入的mac地址</param>
        /// <returns>如果返回非空的串则表可读到相应的dns_name和状态</returns>
        public DnsAndState GetDnsNameAndStateByMac(String mac)
        {
            DnsAndState dnsState=new DnsAndState();
            String strSql = "select dns_name,dns_online from dns_info where dns_mac='" + mac + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            MySqlDataReader dr = db.ExecuteReader(cmd);
            if (dr.Read())
            {
                dnsState.dns_name = dr.GetString(0);
                dnsState.dvr_state= dr.GetBoolean(1);
            }
            dr.Close();
            return dnsState;
        }
        /// <summary>
        /// 查找MAC地址，如果没有返回NULL值。如果有则返回首行首列。
        /// </summary>
        /// <param name="mac"></param>
        /// <returns>-1:没有找到相应记录，1：已绑定，0：没有绑定</returns>
        public DataTable GetNumMac(String mac)
        {
            String strSql = "select *from dns_info  where dns_mac='" + mac + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            DataTable dt= db.ExecuteDataTable(cmd);
            if (dt.Rows.Count == 0)
            {
                return dt;
            }
            else
            {
                return dt;
            }
            //MySqlDataReader dr = db.ExecuteReader(cmd);
        }

        /// <summary>
        /// 根据用户名查找相应的MAC地址信息
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public DataTable LoadAllDataTable(String userName)
        {
            string strSql = "select dns_ip,dns_mac,dns_port,dns_update,dns_online,dns_location from dns_info"+
                " WHERE user_id in (SELECT user_id from user_info WHERE user_name='"+userName+"')";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            return dt;
        }

        public int Insert(dns_infoEntity obj)
        {
            string strSql = "INSERT INTO user_info(user_id,dns_ip," +
                "dns_mac,dns_update,dns_online,dns_macbind," +
                "dns_name,dns_location,dns_tel) VALUES(" + obj.user_id + ",'" + obj.dns_ip + "','" +
                obj.dns_mac + "','" + obj.dns_update + "'," + obj.dns_online + "," +
                obj.dns_macbind + ",'" + obj.dns_name + "','" + obj.dns_location +"','"+obj.dns_tel+"')";

            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            return db.ExecuteNonQuery(cmd);
        }

        public int Update(string mac,int user_id,string dnsName)
        {
            string strSql="UPDATE dns_info SET user_id ="+user_id+
                ",dns_name = '"+dnsName+"' WHERE dns_mac = '"+mac+"'";
            MySqlCommand cmd=db.GetSqlStringCommond(strSql);
            int ret=db.ExecuteNonQuery(cmd);
            return ret;
        }

        public int Delete(string mac)
        {
            string strSql = "delete from dns_info where dns_mac='" + mac + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            int ret = db.ExecuteNonQuery(cmd);
            return ret;
        }

        /// <summary>
        /// 判断域名是否已被绑定，如果被绑定不能再重复绑定。
        /// </summary>
        /// <param name="dnsname"></param>
        /// <returns>0：可以绑定，非0已存在</returns>
        public int BindMac(string dnsname)
        {
            string strSql = "select *from dns_info where dns_name='" + dnsname + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            int ret = Convert.ToInt32(db.ExecuteScalar(cmd));
            return ret;
        }

        /// <summary>
        /// 获取DVR所在地信息
        /// </summary>
        /// <param name="dnsname"></param>
        /// <returns></returns>
        public string GetDvrAddress(string dns_mac)
        {
            string ret = "";
            string strSql = "select dns_location from dns_info where dns_mac='" + dns_mac + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            MySqlDataReader dr = db.ExecuteReader(cmd);
            dr.Read();
            ret = dr.GetString(0);
            dr.Close();
            return ret;
        }

        public int UpdateDns(string mac,string dnsName,string dvrAddr)
        {
            string strSql = "UPDATE dns_info SET dns_name ='" + dnsName +
                "',dns_location = '" + dvrAddr + "' WHERE dns_mac = '" + mac + "'";
            MySqlCommand cmd = db.GetSqlStringCommond(strSql);
            int ret = db.ExecuteNonQuery(cmd);
            return ret;
        }
    }
}
